#!/usr/bin/python
# -*- coding: UTF-8 -*-
import json

import pymysql
import time
import string

# 连接的数据库服务器主机名：
conhost = "124.70.70.127"
# 连接的数据库端口号：
conport = 3306
# 连接的数据库的用户名：
conuser = "root"
# 连接的数据库的密码：
conpasswd = "2021buaa!"
# 连接的数据库名：
condb = "acplat"

author_content = []
paper_content = []
venue_content = []


class LoadDataTestor:

    def __init__(self):
        # 连接数据库
        self.conn = pymysql.connect(host=conhost, port=conport, user=conuser, passwd=conpasswd, db=condb)

    def __del__(self):
        try:
            self.conn.close()
        except:
            pass

    def closedb(self):
        self.conn.close()

    def readfile(self):
        file1 = open("../data/aminer_author.txt", "r", encoding="utf-8")
        for line in file1.readlines():
            data = json.loads(line)
            author_content.append(data)

        file2 = open("../data/aminer_paper.txt", "r", encoding="utf-8")
        for line in file2.readlines():
            data = json.loads(line)
            paper_content.append(data)

        file = open("../data/aminer_venue.txt", "r", encoding="utf-8")
        for line in file.readlines():
            data = json.loads(line)
            venue_content.append(data)

    def insert_author(self):
        temp_cursor = self.conn.cursor()
        list_1 = []
        count = 1
        for temp in author_content:
            author_original_id = temp.get('id')
            author_name = temp.get('name')
            author_n_pubs = temp.get('n_pubs')
            author_n_citation = temp.get('n_citation')
            temp_arr = (author_original_id, author_name, author_n_pubs, author_n_citation)
            list_1.insert(count, temp_arr)
            count += 1
        print(list_1)
        sql_1 = "insert into author(original_id, name, n_pubs, n_citation) values(%s, %s, %s, %s)"
        temp_cursor.executemany(sql_1, list_1)
        self.conn.commit()

    def insert_paper(self):
        temp_cursor = self.conn.cursor()
        list_1 = []
        list_2 = []
        temp_list = []
        count = 1
        count2 = 1
        for temp in paper_content:
            paper_id = temp.get('id')
            paper_title = temp.get('title')
            paper_venue = temp.get('venue')
            paper_venue_raw = None
            if paper_venue is not None:
                paper_venue_raw = paper_venue.get('raw')
            paper_n_citation = temp.get('n_citation')
            paper_year = temp.get('year')
            paper_volume = temp.get('volume')
            paper_issue = temp.get('issue')
            paper_doi = temp.get('doi')
            paper_page_start = temp.get('page_start')
            paper_page_end = temp.get('page_end')
            paper_authors = temp.get('authors')
            rank = 1
            if paper_authors is not None:
                for author in paper_authors:
                    author_id = author.get('id')
                    if author_id is not None:
                        temp_sql = "select id from author where original_id='%s';" % author_id
                        temp_cursor.execute(temp_sql)
                        aid = temp_cursor.fetchone()
                        temp_arr = (aid, paper_id, rank)
                        temp_list.insert(count2, temp_arr)
                        count2 += 1
            temp_arr = (
                paper_id, paper_title, paper_venue_raw, paper_n_citation, paper_year, paper_volume, paper_issue,
                paper_doi,
                paper_page_start, paper_page_end)
            list_1.insert(count, temp_arr)
            count += 1
        print(list_1)
        sql_1 = "insert into paper(original_id, title, n_citation, venueraw, year, volume, issue, DOI, page_start, page_end) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        temp_cursor.executemany(sql_1, list_1)
        self.conn.commit()
        count2 = 1
        for temp in temp_list:
            poid = temp[1]
            temp_sql = "select id from paper where original_id='%s';" % poid
            temp_cursor.execute(temp_sql)
            pid = temp_cursor.fetchone()
            temp_arr = (temp[0], pid, temp[2])
            list_2.insert(count, temp_arr)
        sql_2 = "insert into author2paper(aid, pid, pubs_r) values(%s, %s, %s)"
        temp_cursor.executemany(sql_2, list_2)
        self.conn.commit()

    def insert_venue(self):
        temp_cursor = self.conn.cursor()
        list_1 = []
        count = 1
        for temp in author_content:
            venue_id = temp.get('id')
            venue_display_name = temp.get('DisplayName')
            venue_normalized_name = temp.get('NormalizedName')
            temp_arr = (venue_id, venue_display_name, venue_normalized_name)
            list_1.insert(count, temp_arr)
            count += 1
        print(list_1)
        sql_1 = "insert into venue(original_id, displayname, normalizedname) values(%s, %s, %s)"
        temp_cursor.executemany(sql_1, list_1)
        self.conn.commit()


if __name__ == "__main__":

    test = LoadDataTestor()

    try:
        test.readfile()
        test.insert_author()
        test.insert_venue()
        test.insert_paper()

    finally:
        test.closedb()
